package impl.service;

import impl.MySQL;
import model.MusicData;
import model.MusicList;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class MusicSql {
    private static final String GET_ALL_MUSIC = "select music.title as title, IFNULL(singer.name,'null') as singer, music.time as time, music.url as url, music.count as count from music left join singer on music.singerid = singer.id order by music.count desc;";//查询全部歌曲
    private static final String GET_LOVE_MUISC = "select m.title as title, IFNULL(s.name, 'null') as singer, m.time as time, m.url as url, m.count as count from music m left join singer s on m.singerid = s.id where m.id in (select musicid from lovemusic where userid = ?);";//查询喜欢的歌曲
    private static final String GET_MUSIC = "select m.title as title, IFNULL(s.name, 'null') as singer, m.time as time, m.url as url, m.count as count from music m left join singer s on m.singerid = s.id where m.id = ?;";
    private static final String GET_LATELY_MUSIC_ID = "select musicid from latelymusic where userid = ? order by audiotime desc limit 10;";
    private static final String GET_LIST_NAME = "select id,name from list where userid = ?;";//获取歌单名称
    private static final String GET_LIST_MUSIC_ID = "select music.title as title, IFNULL(singer.name,'null') as singer, music.time as time, music.url as url, music.count as count from music  left join singer on music.singerid = singer.id where music.id in (select musicid from musiclist where listid = ?) order by music.count desc;";
    private static final String UPDATE_MUSIC_COUNT = "update music set count = ? where url = ?;";//更新音乐播放次数
    private static final String SELECT_MUSIC_COUNT = "select count from music where url = ?";//查询音乐播放次数
    private static final String INSERT_MUSIC_TO_LATELY = "insert into latelymusic (userid,musicid,audiotime) values (?,?,NOW());";//添加歌曲到最近播放
    private static final String SELECT_MUSIC_LATELY_IF_NULL = "select * from latelymusic where musicid = ? and userid = ?;";//查询最近播放中是否存在歌曲
    private static final String UPDATE_MUSIC_BY_NOW = "update latelymusic set audiotime = NOW() where userid = ? and musicid = ?;";//更新音乐播放时间到现在
    private static final String INSERT_MUSIC_LIST = "insert into list (userid,name) values (?,?);";//创建歌单
    private static final String DELETE_LIST = "delete from list where id = ?";//删除歌单
    private static final String INSERT_MUSIC_TO_LIST = "insert into musiclist (listid,musicid) values (?,?)";//添加音乐到歌单
    private static final String INSERT_MUSIC_TO_LOVE = "insert into lovemusic (userid,musicid) values (?,?)";//添加歌曲到我喜欢
    private static final String GET_MUSIC_ID = "select id from music where url = ?";
    private static final String SEARCH_MUSIC_NAME = "select music.title as title, IFNULL(singer.name,'null') as singer, music.time as time, music.url as url, music.count as count from music left join singer on music.singerid = singer.id where music.title like ?;";//模糊查询音乐根据歌曲名称
    private static final String SEARCH_MUSIC_SINGER = "select music.title as title, IFNULL(singer.name,'null') as singer, music.time as time, music.url as url, music.count as count from music left join singer on music.singerid = singer.id where singer.name like ?;";
    private static final String DELETE_MUSIC_FOR_LOVE = "delete from lovemusic where musicid = ? and userid = ?";//从我喜欢中删除歌曲
    private static final String DELETE_MUSIC = "delete from `musiclist` where listid = ? and musicid = ?;";//从歌单中删除音乐
    private final static MySQL mySQL;

    static {
        try {
            mySQL = new MySQL();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
    public Boolean delectMusicForLove(Integer id,String musicId)throws SQLException{
        PreparedStatement statement = mySQL.getStatement(DELETE_MUSIC_FOR_LOVE);
        statement.setInt(1,getMusicId(musicId));
        statement.setInt(2,id);
        Integer n = statement.executeUpdate();
        statement.close();
        if (n == 1){
            return true;
        }
        return false;
    }
    public Boolean delectMusicForList(Integer id,String musicId) throws SQLException {
        PreparedStatement statement = mySQL.getStatement(DELETE_MUSIC);
        statement.setInt(1,id);
        statement.setInt(2,getMusicId(musicId));
        Integer n = statement.executeUpdate();
        statement.close();
        if (n == 1){
            System.out.println("删除成功");
            return true;
        }
        return false;
    }
    //查询歌曲
    public List<MusicData> search(String text) throws SQLException {
        StringBuffer search = new StringBuffer("%");
        for (int i = 0; i < text.length(); i++) {
            search.append(text.charAt(i));
            search.append("%");
        }
        List<MusicData> data = new ArrayList<>();
        PreparedStatement statement = mySQL.getStatement(SEARCH_MUSIC_NAME);
        statement.setString(1,search.toString());
        List<MusicData> name = getMusicList(statement.executeQuery());
        statement = mySQL.getStatement(SEARCH_MUSIC_SINGER);
        statement.setString(1,search.toString());
        List<MusicData> singer = getMusicList(statement.executeQuery());
        statement.close();
        data.addAll(name);
        data.addAll(singer);
        return data;
    }
    public Boolean insertToLove(String url,Integer id) throws SQLException {
        PreparedStatement statement = mySQL.getStatement(INSERT_MUSIC_TO_LOVE);
        return insertMusic(url,id,statement);
    }
    public Boolean insertToList(String url,Integer id) throws SQLException {
        PreparedStatement statement = mySQL.getStatement(INSERT_MUSIC_TO_LIST);
        return insertMusic(url,id,statement);
    }

    private Boolean insertMusic(String url,Integer id,PreparedStatement statement) throws SQLException {
        statement.setInt(1,id);
        statement.setInt(2,getMusicId(url));
        Integer n = statement.executeUpdate();
        if (n==1){
            return true;
        }
        return false;
    }

    //创建歌单
    public Boolean createMusicList(String name,Integer userId) throws SQLException {
        System.out.println("name:" + name);
        System.out.println("userId:" + userId);
        PreparedStatement statement = mySQL.getStatement(INSERT_MUSIC_LIST);
        statement.setInt(1,userId);
        statement.setString(2,name);
        Integer n = statement.executeUpdate();
        statement.close();
        System.out.println("创建成功");
        if (n == 1){
            return true;
        }
        return false;
    }

    /**
     * 所有歌曲
     * @return
     * @throws SQLException
     */
    public List<MusicData> getAllMusic() throws SQLException {
        PreparedStatement statement = mySQL.getStatement(GET_ALL_MUSIC);
        List<MusicData> data = getMusicList(statement.executeQuery());
        statement.close();
        return data;
    }

    /**
     * 喜欢的歌曲
     * @param userId
     * @return
     */
    public List<MusicData> getLoveMusic(Integer userId) throws SQLException {
        PreparedStatement statement = mySQL.getStatement(GET_LOVE_MUISC);
        statement.setInt(1,userId);
        List<MusicData> data = getMusicList(statement.executeQuery());
        statement.close();
        return data;
    }

    /**
     * 最近播放
     * @param userId
     * @return
     */
    public List<MusicData> getlatelyMusic(Integer userId) throws SQLException {
        PreparedStatement statement = mySQL.getStatement(GET_LATELY_MUSIC_ID);
        statement.setInt(1,userId);
        ResultSet resultSet = statement.executeQuery();
        List<MusicData> data = new ArrayList<>();
        while (resultSet.next()){
            data.add(getMusic(resultSet.getInt("musicId")));
        }
        resultSet.close();
        statement.close();
        return data;
    }

    /**
     * 歌单歌曲
     * @param listId
     * @return
     */
    public List<MusicData> getMusicList(Integer listId) throws SQLException {
        PreparedStatement statement = mySQL.getStatement(GET_LIST_MUSIC_ID);
        statement.setInt(1,listId);
        List<MusicData> list = getMusicList(statement.executeQuery());
        statement.close();
        return list;
    }

    /**
     * 歌单
     * @param userId
     * @return
     */
    public List<MusicList> getMuiscListName(Integer userId) throws SQLException {
        List<MusicList> data = new ArrayList<>();
        PreparedStatement statement = mySQL.getStatement(GET_LIST_NAME);
        statement.setInt(1,userId);
        ResultSet resultSet = statement.executeQuery();
        while (resultSet.next()){
            MusicList list = new MusicList();
            list.setId(resultSet.getInt("id"));
            list.setName(resultSet.getString("name"));
            System.out.println(list);
            data.add(list);
        }
        resultSet.close();
        statement.close();
        return data;
    }
    public Integer getMusicId(String url) throws SQLException{
        PreparedStatement statement = mySQL.getStatement(GET_MUSIC_ID);
        statement.setString(1,url);
        ResultSet set = statement.executeQuery();
        Integer id = null;
        while (set.next()){
            id = set.getInt("id");
        }
        set.close();
        statement.close();
        return id;
    }
    public void updateCount(String url) throws SQLException {
        PreparedStatement statement = mySQL.getStatement(SELECT_MUSIC_COUNT);
        statement.setString(1,url);
        ResultSet resultSet = statement.executeQuery();
        Integer count = null;
        while (resultSet.next()){
            count = resultSet.getInt("count");
        }
        statement = mySQL.getStatement(UPDATE_MUSIC_COUNT);
        statement.setInt(1,count+1);
        statement.setString(2,url);
        if (statement.executeUpdate() == 1){
            System.out.println("更新成功");
        }else {
            System.out.println("更新失败");
        }
        statement.close();
    }
    public void updateLately(Integer userId,Integer musicId) throws SQLException {
        PreparedStatement statement = mySQL.getStatement(SELECT_MUSIC_LATELY_IF_NULL);
        statement.setInt(1,musicId);
        statement.setInt(2,userId);
        ResultSet resultSet = statement.executeQuery();
        int n = 0;
        while (resultSet.next()){
            n++;
        }
        resultSet.close();
        if (n == 0){
            statement = mySQL.getStatement(INSERT_MUSIC_TO_LATELY);
        }else {
            statement = mySQL.getStatement(UPDATE_MUSIC_BY_NOW);
        }
        statement.setInt(1,userId);
        statement.setInt(2,musicId);
        n = statement.executeUpdate();
        if (n == 1){
            System.out.println("更新成功");
        }else {
            System.out.println("更新失败");
        }
        statement.close();

    }
    private MusicData getMusic(Integer musicId) throws SQLException {
        PreparedStatement statement = mySQL.getStatement(GET_MUSIC);
        statement.setInt(1,musicId);
        ResultSet resultSet = statement.executeQuery();
        MusicData musicData = new MusicData();
        while (resultSet.next()) {
            musicData.setTitle(resultSet.getString("title"));
            musicData.setSinger(resultSet.getString("singer"));
            musicData.setTime(resultSet.getString("time"));
            musicData.setUrl(resultSet.getString("url"));
            musicData.setCount(resultSet.getInt("count"));
        }
        resultSet.close();
        statement.close();
        return musicData;
    }
    private List<MusicData> getMusicList(ResultSet resultSet) throws SQLException {
        List<MusicData> data = new ArrayList<>();
        while (resultSet.next()) {
            MusicData musicData = new MusicData();
            musicData.setTitle(resultSet.getString("title"));
            musicData.setSinger(resultSet.getString("singer"));
            musicData.setTime(resultSet.getString("time"));
            musicData.setUrl(resultSet.getString("url"));
            musicData.setCount(resultSet.getInt("count"));
            data.add(musicData);
        }
        resultSet.close();
        return data;
    }
    public Boolean deleteList(Integer id) throws SQLException {
        PreparedStatement statement = mySQL.getStatement(DELETE_LIST);//删除歌单
        statement.setInt(1,id);
        Integer i = statement.executeUpdate();
        statement.close();
        return true;
    }
}
